
package com.huizhan.web.dao;
 
import org.springframework.data.domain.*;
import org.springframework.data.jpa.repository.*;
import org.springframework.stereotype.Repository;
import com.huizhan.web.model.*;


@Repository 
public interface DocumentRecordDao extends JpaRepository<DocumentRecord,Long>
{
    /**
     * 分页查证件记录集合
     * @param name 名称
     * @param phone 手机号
     * @param document_Type_Id 证件类型ID
     * @param project_Id 项目ID
     * @param pageable 分页参数
     * @return 证件记录集合
     */
    @Query(
        value = "SELECT * FROM document_record"
            + " WHERE (Distribute_Id IS NULL OR Distribute_Id <1)  AND (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (phone LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (project_Id =?3 OR ?3 = '') "
            + " AND (document_Type_Id =?4 OR ?4 = 0 )", 
        countQuery = "SELECT COUNT(1) FROM document_record"
            + " WHERE (Distribute_Id IS NULL OR Distribute_Id <1)  AND  (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (phone LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (project_Id =?3 OR ?3 = '') "
            + " AND (document_Type_Id =?4 OR ?4 = 0 )",
        nativeQuery = true
    )
    Page<DocumentRecord> findByWhere(String name,String phone,String project_Id,Long document_Type_Id,Pageable  pageable);


    /**
     * 分页查证件记录集合
     * @param name 名称
     * @param phone 手机号
     * @param document_Type_Id 证件类型ID
     * @param project_Id 项目ID
     * @param pageable 分页参数
     * @return 证件记录集合
     */
    @Query(
        value = "SELECT * FROM document_record"
            + " WHERE Distribute_Id >0  AND (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (phone LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (project_Id =?3 OR ?3 = '') "
            + " AND (document_Type_Id =?4 OR ?4 = 0 )", 
        countQuery = "SELECT COUNT(1) FROM document_record"
            + " WHERE Distribute_Id >0  AND  (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (phone LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (project_Id =?3 OR ?3 = '') "
            + " AND (document_Type_Id =?4 OR ?4 = 0 )",
        nativeQuery = true
    )
    Page<DocumentRecord> findByWhere1(String name,String phone,String project_Id,Long document_Type_Id,Pageable  pageable);

    
    /**
     * 根据类型ID和手机号获取证件记录
     * @param documentTypeId
     * @param phone
     * @return
     */
    DocumentRecord findByDocumentTypeIdAndPhone(Long documentTypeId,String phone);
}
